<?php 
// +----------------------------------------------------------------------
// | Class  Mysql操作基类
// +----------------------------------------------------------------------
// | Copyright (c) 2020
// +----------------------------------------------------------------------

defined('SYS_DEBUG') or define('SYS_DEBUG', true); // 调试开关

class DbMySqli {

    private $_result;
    private $_dbCon;
    public $db_log;
    public $db_name;
    public $db_fix;

    //定义静态变量保存当前类的实例
    private static $instance;

    //防止在外部实例化
    private function __construct($config){
        $this->db_fix = $config['db_fix'];
        $this->db_name = $config['db_name'];
        $this->connect($config['db_host'], $config['db_user'], $config['db_pwd'], $config['db_name'], $config['db_port'], $config['db_char']);
    }

    //防止在外部克隆
    private function __clone(){

    }

    //通过静态公有的方法获取这个类的实例
    public static function getInstance($config=array()){
        //当前对象不属于当前例就实例化
        if (!self::$instance instanceof self) {
            self::$instance = new self($config);
        }
        return self::$instance;
    }

	/**
	 * @fun   连接数据库
	 * @desc  
	 */
    public function connect($address, $account, $pwd, $dbname, $port, $char){
        try {
            $this->_dbCon = mysqli_connect($address, $account, $pwd, $dbname, $port);
            if(!$this->_dbCon){
                if(SYS_DEBUG){exit('数据库目标拒绝链接失败');}else{exit;}
            }
        } catch (mysqli_sql_exception $e) {
            if(SYS_DEBUG){exit('数据库链接异常:'.$e->getMessage());}else{exit;}
        }

        if(!$this->_dbCon){
			exit('Error('.mysqli_connect_errno().'):'.mysqli_connect_error());
        }else{
            mysqli_query($this->_dbCon,'set names '.strtolower($char));
            return 1;
        }
    }

	/**
	 * @fun   where 条件处理
	 * @desc  
	 */
	private function whereTo($params){
		$whereSql = '';
		if(!isset($params['where']) || empty($params['where'])){
			return $whereSql;
		}
		if(is_array($params['where'])){
            foreach($params['where'] as $k=>$v){
                if(!preg_match('/^[A-Za-z]+/',$k)){
                    exit('Error : 查询语句没有指定字段名');
                }
                $fieldVal = $symbol = '';
                if(is_array($v)){
                    $symbol = isset($v[0]) ? $v[0] : '=';
                    $fieldVal = isset($v[1]) ? $v[1] : '';
                }else if(is_string($v) || is_numeric($v)){
                    $symbol = '=';
                    $fieldVal = strval($v);
                }
                if($symbol){
                    $fieldVal = $this->sqlStr($fieldVal);
                    $whereSql .= ' and '.$k.' '.$symbol.' '.$fieldVal;
                }
            }
        }else{
            $whereSql = ' '.$params['where'];
        }
		return $whereSql;
	}

    /**
     * @fun   where 函数条件处理
     * @desc
     */
    private function whereFun($params){
        $whereSql = '';
        if(isset($params['locate'])){
            if(is_array($params['locate'])){
                $locateOrArr = array();
                foreach($params['locate'] as $k=>$v){
                    if(is_array($v)){
                        $locateOrArr[$k] = isset($v[0]) ? $v[0] : '';
                    }else{
                        $k = $this->sqlVal($k);
                        $v = $this->sqlStr($v);
                        $whereSql .= " and locate ($v,$k) > 0 ";
                    }
                }
                // 有or
                if(!empty($locateOrArr)){
                    $whereSql .= ' and (';
                    foreach($locateOrArr as $k=>$v){
                        $k = $this->sqlVal($k);
                        $v = $this->sqlStr($v);
                        $whereSql .= " locate ($v,$k) > 0 or";
                    }
                    $whereSql = substr($whereSql,0,-2);
                    $whereSql .= ' ) ';
                }
            }
        }
        if(isset($params['gt'])){
            if(is_array($params['gt'])){
                foreach($params['gt'] as $k=>$v){
                    $whereSql .= " and $k > '$v' ";
                }
            }
        }
        if(isset($params['lt'])){
            if(is_array($params['lt'])) {
                foreach ($params['lt'] as $k => $v) {
                    $whereSql .= " and $k < '$v' ";
                }
            }
        }
        if(isset($params['gts'])){
            if(is_array($params['gts'])) {
                foreach ($params['gts'] as $k => $v) {
                    $whereSql .= " and $k >= '$v' ";
                }
            }
        }
        if(isset($params['lts'])){
            if(is_array($params['lts'])) {
                foreach ($params['lts'] as $k => $v) {
                    $whereSql .= " and $k <= '$v' ";
                }
            }
        }
        if(isset($params['in'])){
            if(is_array($params['in'])) {
                foreach ($params['in'] as $k => $v) {
                    $in = '';
                    if (is_array($v)) {
                        $v = str_replace(array('"', "'", '-', "\r\n", "\r", "\n", PHP_EOL), '', $v);
                        $in = implode("','", $v);
                        $in = $in ? "'" . $in . "'" : '';
                    } else if (is_string($v) || is_numeric($v)) {
                        $v = str_replace(array('"', "'", '-', "\r\n", "\r", "\n", PHP_EOL), '', $v);
                        $in = str_replace(',', "','", $v);
                        $in = $in ? "'" . $in . "'" : '';
                    }
                    $whereSql .= " and $k in('#in#',$in) ";
                }
            }
        }
        if(isset($params['notin'])){
            if(is_array($params['notin'])) {
                foreach ($params['notin'] as $k => $v) {
                    $in = '';
                    if (is_array($v)) {
                        $v = str_replace(array('"', "'", '-', "\r\n", "\r", "\n", PHP_EOL), '', $v);
                        $in = implode("','", $v);
                        $in = $in ? "'" . $in . "'" : '';
                    } else if (is_string($v) || is_numeric($v)) {
                        $v = str_replace(array('"', "'", '-', "\r\n", "\r", "\n", PHP_EOL), '', $v);
                        $in = str_replace(',', "','", $v);
                        $in = $in ? "'" . $in . "'" : '';
                    }
                    $whereSql .= " and $k not in('#notin#',$in) ";
                }
            }
        }
        if(isset($params['null'])){
            if(is_array($params['null'])) {
                foreach ($params['null'] as $k => $v) {
                    if (is_string($v)) {
                        $f = $v ? strtolower($v) : 'is';
                        $f = $f == 'is' ? 'is' : 'is not';
                        $whereSql .= " and $k $f null ";
                    }
                }
            }
        }
        if(isset($params['or'])){
            if(is_array($params['or'])) {
                $orStart = ' and ( ';
                $orStr = '';
                foreach ($params['or'] as $k => $v) {
                    if(is_array($v)){
                        $f = isset($v[0]) ? $v[0] : '=';
                        $f = $f == '=' ? '=' : '!=';
                        $val = isset($v[1]) ? $v[1] : '';
                        $orStr .= $orStr ? " or $k $f '$val'" : " $k $f '$val'";
                    }else{
                        $orStr .= $orStr ? " or $k = '$v'" : " $k = '$v'";
                    }
                }
                $orEnd = ' ) ';
                $whereSql .= $orStart . $orStr . $orEnd;
            }
        }
        return $whereSql;
    }

	/**
	 * @fun   group by 分组处理
	 * @desc  
	 */
	private function groupTo($params){
		$groupSql = '';
		if(isset($params['group']) && is_string($params['group'])){
			$groupSql = ' group by '.$this->sqlVal($params['group']).' ';
		}
		return $groupSql;
	}
	
	/**
	 * @fun   order by 排序处理
	 * @desc  
	 */
	private function orderTo($params){
		$orderSql = '';
		if(isset($params['order']) && !empty($params['order'])){ // 定义了排序
		    if(is_string($params['order'])){ // 字符串时
                $orderSql = ' order by '.$this->sqlVal($params['order']).' ';
            }else if(is_array($params['order'])){  // 数组时
                if(is_string(key($params['order']))){  // 一维数组时（单个字段排序）
                    $orderSql = ' order by '.key($params['order']).' '.$this->sqlVal(reset($params['order'])).' ';
                }else{ // 二维数组时（多个字段排序）
                    $orderSql = ' order by ';
                    foreach($params['order'] as $k=>$v){
                        $orderSql .= key($v).' '.reset($v).',';
                    }
                    $orderSql = rtrim($orderSql, ",");
                }
            }
		}
		return $orderSql;
	}
	
	/**
	 * @fun   limit 分页处理
	 * @desc  
	 */
	private function limitTo($params){
		$limitSql = '';
		if(isset($params['limit']) && !empty($params['limit'])){
		    if(is_array($params['limit'])){
                $start = intval(reset($params['limit']));
                $endArr = array_slice($params['limit'],1,1);
                $end = intval(reset($endArr));
                $start = $start >= 0 ? $start : 0;
                if($end > 0){
                    $limitSql = ' limit '.$start.','.$end.' ';
                }
            }else{
		        $limit = intval($params['limit']);
		        if($limit > 0){
                    $limitSql = ' limit '.$limit.' ';
                }
            }

		}
		return $limitSql;
	}
	
	/**
	 * @fun   start-end 分页处理
	 * @desc  
	 */
	private function startEnd($params){
		$limitArr = array();
		if(isset($params['limit']) && !empty($params['limit'])){
			$limits = explode(',',$params['limit']);
			$limitArr['start'] = '0';
			if(isset($limits[1]) && $limits[1]){
				$limitArr['start'] = intval($limits[0]);
				$limitArr['end'] = intval($limits[1]);
			}else{
				$limitArr['end'] = intval($limits[0]);
			}
		}
		return $limitArr;
	}
	
	/**
	 * @fun   sql 表 处理
	 * @desc  
	 */
	private function setTable($params){
		$table = '';
		if(isset($params['table']) && $params['table']){
			$table = $this->sqlVal(strtolower($this->db_fix.$params['table']));
		}
		return $table;
	}
	
	/**
	 * @fun   sql params 处理
	 * @desc  
	 */
	private function sqlSet($params){
		$sqlArr['field'] = '*';
		if(isset($params['field']) && $params['field']){
			$sqlArr['field'] = $this->sqlVal(strtolower($params['field']));
		}
		$sqlArr['table'] = $this->setTable($params);
		$sqlArr['as'] = '';
		if(isset($params['as']) && $params['as']){
			$sqlArr['as'] = $this->sqlVal(strtolower($params['as']));
		}
		$sqlArr['leftJoin'] = '';
		if(isset($params['leftjoin']) && $params['leftjoin']){
			foreach($params['leftjoin'] as $k=>$v){
			    if(is_array($v)){
                    foreach($v as $k2=>$v2){
                        $sqlArr['leftJoin'] .= ' left join '.$this->db_fix.$k.' '.$this->sqlVal(strtolower($v2));
                    }
                }else{
                    $sqlArr['leftJoin'] .= ' left join '.$this->db_fix.$k.' '.$this->sqlVal(strtolower($v));
                }
			}
		}
		$sqlArr['rightJoin'] = '';
		if(isset($params['rightjoin']) && $params['rightjoin']){
			foreach($params['rightjoin'] as $k=>$v){
                if(is_array($v)){
                    foreach($v as $k2=>$v2){
                        $sqlArr['rightJoin'] .= ' right join '.$this->db_fix.$k.' '.$this->sqlVal(strtolower($v2));
                    }
                }else{
                    $sqlArr['rightJoin'] .= ' right join '.$this->db_fix.$k.' '.$this->sqlVal(strtolower($v));
                }
			}
		}
		$sqlArr['innerJoin'] = '';
		if(isset($params['innerjoin']) && $params['innerjoin']){
			foreach($params['innerjoin'] as $k=>$v){
                if(is_array($v)){
                    foreach($v as $k2=>$v2){
                        $sqlArr['innerJoin'] .= ' inner join '.$this->db_fix.$k.' '.$this->sqlVal(strtolower($v2));
                    }
                }else{
                    $sqlArr['innerJoin'] .= ' inner join '.$this->db_fix.$k.' '.$this->sqlVal(strtolower($v));
                }
			}
		}
		return $sqlArr;
	}
	
	/**
	 * @fun   sql data 处理
	 * @desc  
	 */
	private function setData($params){
		if(isset($params['data']) && $params['data']){
			return $params['data'];
		}
		return array();
	}

	/**
	 * @fun   查询单条
	 * @desc  
	 */
    public function find($params){
		$sqlArr = $this->sqlSet($params);
		$w = $this->whereTo($params);
        $f = $this->whereFun($params);
		$o = $this->orderTo($params);
		$sql = 'select '.$sqlArr['field'].' from '.$sqlArr['table'].' '.$sqlArr['as'].' ';
		$sql .= $sqlArr['leftJoin'].' '.$sqlArr['rightJoin'].' '.$sqlArr['innerJoin'].' ';
		$sql .= ' where 1=1 '.$w.' '.$f.' '.$o;
		return $this->query($sql,true,true);
    }
	
	/**
	 * @fun   查询-默认查询
	 * @desc  支持limit分页
	 */
    public function select($params){
		$sqlArr = $this->sqlSet($params);
		$w = $this->whereTo($params);
		$f = $this->whereFun($params);
		$g = $this->groupTo($params);
		$o = $this->orderTo($params);
		$l = $this->limitTo($params);
		$sql = 'select '.$sqlArr['field'].' from '.$sqlArr['table'].' '.$sqlArr['as'].' ';
		$sql .= $sqlArr['leftJoin'].' '.$sqlArr['rightJoin'].' '.$sqlArr['innerJoin'].' ';
		$sql .= ' where 1=1 '.$w.' '.$f.' '.$g.' '.$o.' '.$l;
		return $this->query($sql,true);
    }
	
	/**
	 * @fun   查询-起止分页
	 * @desc  支持起始分页
	 */
    public function findAll($params){
		$sqlArr = $this->sqlSet($params);
		$w = $this->whereTo($params);
        $f = $this->whereFun($params);
		$g = $this->groupTo($params);
		$o = $this->orderTo($params);
		$l = $this->startEnd($params);
		$sqlStart = '';
		$sqlEnd = '';
		if(!empty($l)){
			$start = $l['start'];
			$end = $l['end'];
			$sqlStart = "select view_t1.* from ( select @rowno:=@rowno+1 as R, ymd_t.* from ( ";
			$sqlEnd = ") ymd_t ) view_t1 where view_t1.r>$start and view_t1.r<=$end";
		}
		$sqlContent = 'select @rowno:=0,'.$sqlArr['field'].' from '.$sqlArr['table'].' '.$sqlArr['as'].' ';
		$sqlContent .= $sqlArr['leftJoin'].' '.$sqlArr['rightJoin'].' '.$sqlArr['innerJoin'].' ';
		$sqlContent .= ' where 1=1 '.$w.' '.$f.' '.$g.' '.$o;
		$sql = $sqlStart.$sqlContent.$sqlEnd;
		return $this->query($sql,true);
    }
	
	/**
	 * @fun   新增
	 * @desc  条件必须
	 */
    public function add($params){
		$table = $this->setTable($params);
		$data  = $this->setData($params);
		$field = '';
		$value = '';
		if(!empty($data)){
			foreach($data as $k=>$v){
                $k = $this->sqlVal($k);
				$v = $this->sqlStr($v);
				$field .= $k.',';
				$value .= $v.",";
			}
			$field = rtrim($field, ',');
			$value = rtrim($value, ',');
			if($field && $value){
				$sql = "insert into $table ($field) values ($value)";
				return $this->query($sql, false);
			}
		}
		return false;
	}
	
	/**
	 * @fun   修改
	 * @desc  
	 */
    public function update($params){
		$table = $this->setTable($params);
		$data  = $this->setData($params);
		$where = $this->whereTo($params);
		$set = '';
		if(!empty($data)){
			foreach($data as $k=>$v){
                $k = $this->sqlVal($k);
				$v = $this->sqlStr($v);
				$set .= "".$k."=".$v.",";
			}
			//$set = rtrim($set, ',');
			$set = substr($set, 0, -1);
			
			if($where){
				$sql = "update $table set $set where 1=1 $where";
				return $this->query($sql,false);
			}
		}
		return false;
	}
	
	/**
	 * @fun   删除
	 * @desc  条件必须
	 */
    public function del($params){
		$table = $this->setTable($params);
		$w = $this->whereTo($params);
		if($w){
            $sql = "delete from $table where 1=1 $w";
			return $this->query($sql,false);
		}
		return false;
    }
	
	/**
	 * @fun   返回作用域主键
	 * @desc  
	 */
    public function returnId($returnField = 'id'){
        $returnField = $returnField ? $returnField : 'id';
		$sql = "SELECT @@IDENTITY $returnField;";
		$ids = $this->query($sql,true, true);
		return isset($ids['id']) ? intval($ids['id']) : 0;
	}
	
	/**
	 * @fun   自定义SQL执行
	 * @desc  
	 */
    public function query($sql, $type = false, $single = false){
        if($this->db_log){
            SYS_DB_LOGER($sql);
            $debug = array();
            $errArr = debug_backtrace();
            if($errArr){
                foreach($errArr as $v){
                    $file = isset($v['file']) ? 'ErrFile in '.$v['file'] : '';
                    $line = isset($v['line']) ? ' on line '.$v['line'] : '';
                    $class = isset($v['class']) ? ' & Class is '.$v['class'] : '';
                    $function = isset($v['function']) ? ' and Function is '.$v['function'] : '';
                    $debug[] = $file.$line.$class.$function;
                }
            }
            SYS_DB_LOGER($debug);
        }
        // 增删改
        if(!$type){
            $this->_result = $this->_dbCon->multi_query($sql);
            if(!$this->_result){
                if(SYS_DEBUG){exit('Error: '.$this->getError());}else{exit;}
            }
            return true;
        }
        // 查询
        $table = array();
        $field = array();
        $result = array();
        $tempResults = array();
        try {
            $this->_result = mysqli_query($this->_dbCon, $sql);
            if(!$this->_result){
                exit('Error: '.$this->getError());
            }
            $numOfFields = mysqli_field_count($this->_dbCon);
            for($i = 0; $i < $numOfFields; ++$i) { array_push($table,mysqli_fetch_field_direct($this->_result, $i));
                array_push($field,mysqli_fetch_field_direct($this->_result, $i));
            }
            while($row = mysqli_fetch_row($this->_result)){
                for($i = 0;$i < $numOfFields; ++$i){
                    $table[$i]->table = ucfirst($table[$i]->table);
                    //$tempResults[$table[$i]->table][$field[$i]->name] = $row[$i];
                    $tempResults[$field[$i]->name] = $row[$i];
                }
                if($single){
                    mysqli_free_result($this->_result);
                    return $tempResults;
                }
                array_push($result,$tempResults);
            }
            // 从结果集中取得行，然后释放结果内存
            mysqli_free_result($this->_result);
            //$this->dbclose();
            return($result);
        }catch (Exception $e){
            if(SYS_DEBUG){exit('Error: '.$this->getError());}else{exit;}
        }
    }

    /**
     * Notes:通用数据表_添加
     * User: ZhuHaili
     */
    function addData($table, $data, $returnField = ''){
        $sql['data'] = $data;
        $sql['table'] = $table;
        $this->db_log = true;
        $this->add($sql);
        $return  = $this->rowCount();
        if($return){
            if($returnField){
                return $this->returnId($returnField);
            }
            return true;
        }
        return false;
    }

    /**
     * Notes:通用数据表_修改
     * User: ZhuHaili
     */
    function updateData($table, $data, $where, $line = false){
        $sql['data'] = $data;
        $sql['table'] = $table;
        $sql['where'] = $where;
        $result = $this->update($sql);
        if($line){
            return $this->rowCount() > 0 ? $this->rowCount() : 0;
        }
        return $result;
    }

    /**
     * Notes:通用数据表_删除
     * User: ZhuHaili
     */
    function delData($table, $where, $line = false){
        $sql['table'] = $table;
        $sql['where'] = $where;
        $result = $this->del($sql);
        if($line){
            return $this->rowCount() > 0 ? $this->rowCount() : 0;
        }
        return $result;
    }

    /**
     * Notes:获取单表详情
     * User: ZhuHaili
     */
    function findData($table, $where, $field = '*', $orderby = ''){
        $sql['field'] = $field ? $field : '*';
        $sql['table'] = $table;
        $sql['where'] = $where;
        if($orderby){
            $sql['order'] = $orderby;
        }
        return $this->find($sql);
    }

    /**
     * Notes:通用-获取单表数据列表
     * User: ZhuHaili
     */
    function listData($table, $where, $field = '*', $orderby = '', $limit = ''){
        $sql['field'] = $field ? $field : '*';
        $sql['table'] = $table;
        $sql['where'] = $where;
        if($limit){
            $sql['limit'] = $limit;
        }
        if($orderby){
            $sql['order'] = $orderby;
        }
        return $this->select($sql);
    }

	/**
	 * @fun   sql执行后受影响的行数
	 * @desc
	 */
    public function rowCount(){
		return $this->_dbCon->affected_rows;
	}

    /**
     * @fun   转义字符串
     * @desc  默认加引号
     */
    public function sqlVal($v){
        /*$str = @mysqli_real_escape_string($this->_dbCon, $v);
        return $str;*/
        if($v === ''){
            return "''";
        }
        if($v === '0'){
            return "'0'";
        }
        if($v === 0){
            return "'0'";
        }
        if($v === null){
            return "''";
        }
        if($v === false){
            return "''";
        }
        $v = (string)$v;
        $str = htmlspecialchars($v);
        $str = htmlspecialchars_decode($str);
        return $str;
    }

	/**
	 * @fun   转义字符串
	 * @desc  默认加引号
	 */
    public function sqlStr($v){
		//$str = @mysqli_real_escape_string($this->_dbCon,$v);
        //return "'".$str."'";
        if($v === ''){
            return "''";
        }
        if($v === '0'){
            return "'0'";
        }
        if($v === 0){
            return "'0'";
        }
        if($v === null){
            return "''";
        }
        if($v === false){
            return "''";
        }
        $v = (string)$v;
        $str = htmlspecialchars($v);
        $str = htmlspecialchars_decode($str);
        return "'".$str."'";
	}
	
	/**
	 * @fun   获取错误信息
	 * @desc  sql语句执行失败
	 */
    public function getError(){
        $errTxt = mysqli_connect_errno($this->_dbCon).' : '.mysqli_error($this->_dbCon);
		$this->dbclose();
		return $errTxt;
    }
	
	/**
	 * @fun   从数据库断开
	 * @desc  
	 */
    public function dbclose(){
        @mysqli_close($this->_dbCon);
    }

    /**
     * Notes:事务开启
     */
    public function startTrans(){
        $this->_result = mysqli_query($this->_dbCon, "START TRANSACTION");
        if(!$this->_result){
            if(SYS_DEBUG){exit('Error: '.$this->getError());}else{exit;}
        }
        return true;
    }

    /**
     * Notes:事务提交
     */
    public function commit(){
        $this->_result = mysqli_query($this->_dbCon, "COMMIT");
        if(!$this->_result){
            if(SYS_DEBUG){exit('Error: '.$this->getError());}else{exit;}
        }
        return true;
    }

    /**
     * Notes:事务回滚
     */
    public function rollback(){
        $this->_result = mysqli_query($this->_dbCon, "ROLLBACK");
        if(!$this->_result){
            if(SYS_DEBUG){exit('Error: '.$this->getError());}else{exit;}
        }
        return true;
    }
}